﻿using System;
using System.Data;
using System.Collections.ObjectModel;
using MockPrjTeam4.Entity;
using MockPrjTeam4.Common;
using System.Data.SqlClient;

namespace MockPrjTeam4.Dao
{
    public class DirectorateDao
    {
        public static Directorate DirectorateObject(IDataReader read)
        {
            return new Directorate
            {
                DirectorateId = Methods.ConvertNumber(read[Directorate.FieldDirectorateId].ToString()),
                DirectorateName = Methods.ConvertString(read[Directorate.FieldDirectorateName].ToString()),
                ShortDescription = Methods.ConvertString(read[Directorate.FieldShortDescription].ToString()),
                AddressId = Methods.ConvertNumber(read[Directorate.FieldAddressId].ToString()),
                BusinessId = Methods.ConvertNumber(read[Directorate.FieldBusinessId].ToString()),
                PhoneNumber = Methods.ConvertString(read[Directorate.FieldPhoneNumber].ToString()),
                TownId = Methods.ConvertNumber(read[Directorate.FieldTownId].ToString()),
                CountyId = Methods.ConvertNumber(read[Directorate.FieldCountyId].ToString()),
                CountryId = Methods.ConvertNumber(read[Directorate.FieldCountryId].ToString()),
                FullDescription = Methods.ConvertString(read[Directorate.FieldFullDescription].ToString()),
                ContactId = Methods.ConvertNumber(read[Directorate.FieldContactId].ToString()),
                Fax = Methods.ConvertString(read[Directorate.FieldFax].ToString()),
                Email = Methods.ConvertString(read[Directorate.FieldEmail].ToString()),
                WebAddress = Methods.ConvertString(read[Directorate.FieldWebAddress].ToString()),
                CharityNumber = Methods.ConvertString(read[Directorate.FieldCharityNumber].ToString()),
                CompanyNumber = Methods.ConvertString(read[Directorate.FieldCompanyNumber].ToString()),
                IsActive = Methods.ConvertBoolean(read[Directorate.FieldIsActive].ToString()),
                OrganisationId = Methods.ConvertNumber(read[Directorate.FieldOrganisationId].ToString()),
                PostCode = Methods.ConvertString(read[Directorate.FieldPostCode].ToString()),
                AddressLine1 = Methods.ConvertString(read[Directorate.FieldAddressLine1].ToString()),
                AddressLine2 = Methods.ConvertString(read[Directorate.FieldAddressLine2].ToString()),
                AddressLine3 = Methods.ConvertString(read[Directorate.FieldAddressLine3].ToString())
            };
        }


        public static DataTable GetTableDirectorates(bool inActive, int id)
        {
            string query = string.Format("SELECT DirectorateId, DirectorateName, AddressLine1 , " +
                                            "PostCode, FirstName + ' ' + Surname AS ContactName, Directorates.IsActive " +
                                            "AS IsActive " +
                                            "FROM Directorates " +
                                            "LEFT JOIN Contacts ON Contacts.ContactId = Directorates.ContactId ");
            if (inActive == true)
            {
                return SqlHelper.ExecuteQuery(string.Format(query + "WHERE OrganisationId={0} ORDER BY DirectorateName", id));
            }
            else
            {
                query += string.Format(" WHERE Directorates.IsActive='true' AND OrganisationId={0} ORDER BY DirectorateName", id);
                return SqlHelper.ExecuteQuery(query);
            }
        }


        public static DataTable Search(string range, bool inActive, int id)
        {
            string query = string.Format("SELECT DirectorateId, DirectorateName, AddressLine1, " +
                            "PostCode, FirstName + ' ' + Surname AS ContactName, Directorates.IsActive " +
                            "AS IsActive " +
                            "FROM Directorates " +
                            "LEFT JOIN Contacts ON Contacts.ContactId = Directorates.ContactId " +
                            "WHERE DirectorateName LIKE @DirName AND DirectorateId=@DirId ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DirName", range),
                new SqlParameter("@DirId", id)
            };
            if (inActive == true)
            {
                return SqlHelper.ExecuteQuery(query, parameter);
            }
            else
            {
                query += string.Format(" AND Directorates.IsActive='true'");
                return SqlHelper.ExecuteQuery(query, parameter);
            }
        }


        public static bool SetDirActive(int DirId)
        {
            string query = string.Format("UPDATE Directorates SET IsActive='true' WHERE DirectorateId=@DirId ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DirId", DirId)
            };
            try
            {
                SqlHelper.ExecuteQuery(query, parameter);
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }


        public static Directorate SearchDirName(string DirName)
        {
            string query = string.Format("SELECT * FROM Directorates WHERE DirectorateName=@DirName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DirName", DirName)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            if (read.Read())
            {
                return DirectorateObject(read);
            }
            else
            {
                return null;
            }
        }


        public static bool AddDirectorate(Directorate Dir, int orgId)
        {
            string query = string.Format(
                "INSERT INTO Directorates(DirectorateName, ShortDescription, AddressId, BusinessId, " +
                "PhoneNumber, TownId, CountyId, Countryid, FullDescription, ContactId, " +
                "Fax, Email, WebAddress, CharityNumber, CompanyNumber, OrganisationId, " +
                "IsActive, PostCode, AddressLine1, AddressLine2, AddressLine3) " +
                "VALUES(@DirName, @ShortDes, @AddId, @BusinessId, @PhoneNum, @TownId, @CountyId, @CountryId, " +
                "@FullDes, @ContactId, @Fax, @Email, @WebAdd, @CharityNum, @CompanyNum, @OrgId, " +
                "@IsActive, @PostCode, @AddLine1, @AddLine2, @AddLine3)");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DirName", Dir.DirectorateName),
                new SqlParameter("@ShortDes", Dir.ShortDescription),
                new SqlParameter("@AddId", Dir.AddressId),
                new SqlParameter("@BusinessId", Dir.BusinessId),
                new SqlParameter("@PhoneNum", Dir.PhoneNumber),
                new SqlParameter("@TownId", Dir.TownId),
                new SqlParameter("@CountyId", Dir.CountyId),
                new SqlParameter("@CountryId", Dir.CountryId),
                new SqlParameter("@FullDes", Dir.FullDescription),
                new SqlParameter("@Fax", Dir.Fax),
                new SqlParameter("@Email", Dir.Email),
                new SqlParameter("@WebAdd", Dir.WebAddress),
                new SqlParameter("@CharityNum", Dir.CharityNumber),
                new SqlParameter("@CompanyNum", Dir.CompanyNumber),
                new SqlParameter("@OrgId", orgId),
                new SqlParameter("@IsActive", Dir.IsActive),
                new SqlParameter("@PostCode", Dir.PostCode),
                new SqlParameter("@AddLine1", Dir.AddressLine1),
                new SqlParameter("@AddLine2", Dir.AddressLine2),
                new SqlParameter("@AddLine3", Dir.AddressLine3)
            };

            if (Dir.ContactId != -1)
                parameter.Add(new SqlParameter("@ContactId", Dir.ContactId));
            else
                parameter.Add(new SqlParameter("@ContactId", DBNull.Value));

            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }


        public static Directorate Search(int DirName)
        {
            string query = string.Format("SELECT * FROM Directorates WHERE DirectorateId=@DirName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DirName", DirName)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            if (read.Read())
            {
                return DirectorateObject(read);
            }
            else
            {
                return null;
            }
        }


        public static DataTable SearchTableDir(int DirId)
        {
            string query = string.Format("SELECT Directorates.AddressId AS AddressId, Directorates.AddressLine1, Directorates.AddressLine2, " +
                "Directorates.AddressLine3, Directorates.PostCode, " +
                "TownName, CountyName, Directorates.BusinessId AS BusinessId, BusinessName, SICCode, FirstName + ' ' + Surname AS ContactName, " +
                "CountryName, Addresses.TownId AS TownId, Towns.CountyId AS CountyId, Countries.CountryId AS CountryId " +
                "FROM Directorates " +
                "LEFT JOIN Addresses ON Directorates.AddressId = Addresses.AddressId " +
                "LEFT JOIN Towns ON Addresses.TownId = Towns.TownId " +
                "LEFT JOIN Counties ON Towns.CountyId = Counties.CountyId " +
                "LEFT JOIN Countries ON Counties.CountryId = Countries.CountryId " +
                "LEFT JOIN Contacts ON Directorates.ContactId = Contacts.ContactId " +
                "LEFT JOIN BusinessTypes ON Directorates.BusinessId = BusinessTypes.BusinessId " +
                "WHERE DirectorateId=@DirId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DirId", DirId)
            };
            try
            {
                return SqlHelper.ExecuteQuery(query, parameter);
            }
            catch (System.Exception ex)
            {
                return null;
            }
        }

        public static bool EditDirectorate(Directorate Dir, int orgId)
        {
            string query = string.Format(
                "UPDATE Directorates SET DirectorateName = @DirName, ShortDescription = @ShortDes, " +
                "AddressId = @AddId, BusinessId = @BusinessId, " +
                "PhoneNumber = @PhoneNum, TownId = @TownId, CountyId = @CountyId, " +
                "Countryid = @CountryId, FullDescription = @FullDes, " +
                "ContactId = @ContactId, Fax = @Fax, Email = @Email, WebAddress = @WebAdd, " +
                "CharityNumber = @CharityNum, CompanyNumber = @CompanyNum, " +
                "IsActive = @IsActive, " +
                "PostCode=@PostCode, " +
                "AddressLine1=@AddLine1, " +
                "AddressLine2=@AddLine2, " +
                "AddressLine3=@AddLine3 " +
                "WHERE DirectorateId=@DirId " +
                "AND OrganisationId=@OrgId"
                );
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DirId", Dir.DirectorateId),
                new SqlParameter("@DirName", Dir.DirectorateName),
                new SqlParameter("@ShortDes", Dir.ShortDescription),
                new SqlParameter("@AddId", Dir.AddressId),
                new SqlParameter("@BusinessId", Dir.BusinessId),
                new SqlParameter("@PhoneNum", Dir.PhoneNumber),
                new SqlParameter("@TownId", Dir.TownId),
                new SqlParameter("@CountyId", Dir.CountyId),
                new SqlParameter("@CountryId", Dir.CountryId),
                new SqlParameter("@FullDes", Dir.FullDescription),
                new SqlParameter("@Fax", Dir.Fax),
                new SqlParameter("@Email", Dir.Email),
                new SqlParameter("@WebAdd", Dir.WebAddress),
                new SqlParameter("@CharityNum", Dir.CharityNumber),
                new SqlParameter("@CompanyNum", Dir.CompanyNumber),
                new SqlParameter("@IsActive", Dir.IsActive),
                new SqlParameter("@OrgId", orgId),
                new SqlParameter("@PostCode", Dir.PostCode),
                new SqlParameter("@AddLine1", Dir.AddressLine1),
                new SqlParameter("@AddLine2", Dir.AddressLine2),
                new SqlParameter("@AddLine3", Dir.AddressLine3)
            };
            if (Dir.ContactId == -1) parameter.Add(new SqlParameter("@ContactId", DBNull.Value));
            else parameter.Add(new SqlParameter("@ContactId", Dir.ContactId));

            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }
    }
}